const mysql = require('mysql2/promise');

async function checkConnection(dbConfig) {
    // const dbConfig = {
    //     host: 'localhost',       // 数据库主机
    //     user: 'your_username',   // 数据库用户名
    //     password: 'your_password', // 数据库密码
    //     database: 'your_database'  // 数据库名称
    // };
    let connection;

    try {
        // 创建连接
        connection = await mysql.createConnection(dbConfig);
        console.log('Mysql测试连接成功！');
		return {success: true, msg: '测试连接成功！'}
    } catch (error) {
        console.error('Mysql测试连接失败:', error.message);
        return {success: false, msg: error.message}
    } finally {
        if (connection) {
            await connection.end(); // 关闭连接
        }
    }
}

async function queryDatabase(dbConfig, params) {
    let connection;

    try {
        // 创建连接
        connection = await mysql.createConnection(dbConfig);
		//查询参数
		const paramValues = [];
		//查询sql
		let query = 'SELECT '
					+ 'product_name as productName, '
					+ 'cgid, '
					+ 'device_alias as deviceAlias, '
					+ 'pk, '
					+ 'dn, '
					+ 'sec as ds, '
					+ 'dm.merchant_id as merchantId, '
					+ 'dm.merchant_name as merchantName, '
					+ 'ds.store_id as storeId, '
					+ 'ds.store_name as storeName '
					+ 'FROM device_info dd '
					+ 'LEFT JOIN data_merchant dm ON dd.bind_merchant_id = dm.merchant_id '
					+ 'LEFT JOIN data_store ds ON dd.bind_store_id = ds.store_id WHERE 1=1 ';
		//商家id
		if (params.merchantId) {
			query += 'AND dd.bind_merchant_id = ? ';
			paramValues.push(params.merchantId)
		} else {
			throw new Error('商家id不能为空')
		}
		//product_key
		if (params.pk) {
			query += 'AND dd.pk = ? ';
			paramValues.push(params.pk)
		} else {
			throw new Error('ProductKey不能为空')
		}
		//cgid
		if (params.cgid) {
			query += 'AND dd.cgid = ? '
			paramValues.push(params.cgid)
		}
		//device_name
		if (params.dn) {
			query += 'AND dd.dn = ? '
			paramValues.push(params.dn)
		}
		//门店id
		if (params.sotreId) {
			query += 'AND dd.bind_store_id = ? '
			paramValues.push(params.sotreId)
		}
		//执行查询
		console.log('execute sql: ' + query)
        const [rows] = await connection.execute(query, paramValues);
		console.log('result rows: ' + rows.length)
        return {success: true, data: rows, msg: '查询成功'}; // 返回查询结果
    } catch (error) {
        console.error('查询失败:', error.message);
        return {success: false, data: null, msg: error.message}; // 查询失败，返回 null
    } finally {
        if (connection) {
            await connection.end(); // 关闭连接
        }
    }
}

// 示例调用
// await checkConnection(); // 检查连接

// // 执行查询
// const query = 'SELECT * FROM your_table WHERE some_column = ?';
// const params = ['some_value'];
// const results = await queryDatabase(query, params);

// if (results) {
// 	console.log('查询结果:', results);
// }

// 导出方法
module.exports = {
	checkConnection,
	queryDatabase
};